import sqlite3
# commit()--事务提交
# rollback()--事务回滚
# close()--关闭一个数据库连接
# cursor()--创建一个游标

# '评论人': user_name,
# '评论头像': user_face,
# '评论时间': user_date,
# '评论内容': user_text,
# '购买商品类型': info,
# '评论视频截图': videoimg,
# '评论视频': videoUrl,
# '好评图片': imglist,

#创建表
from web.settings import BASE_DIR


def initdb(cursor):
    create_table_sql = '''create table IF NOT EXISTS happy(
                        'ID' INTEGER,
                        '评论人' text,
                        '评论头像' text,
                        '评论时间' text,
                        '评论内容' text unique,
                        '购买商品类型' text,
                        '评论视频截图' text,
                        '评论视频' text,
                        '好评图片' text
                        )'''
    cursor.execute(create_table_sql)  # 执行这个语句

#增
def insertdb(cursor,id,a,b,c,d,e,f,g,h):
    # 二、插入数据：执行语句即可
    # conn = sqlite3.connect("tb.db")
    # cursor=conn.cursor()
    username = "ytouch"
    password = "123456"
    user_id = 1

    insert_data_sql = '''insert or ignore into
                    happy(
                        ID,
                        评论人,
                        评论头像,
                        评论时间,
                        评论内容,
                        购买商品类型,
                        评论视频截图,
                        评论视频,
                        好评图片
                    )
                    values (
                    :shangpinid,
                    :pinglunren,
                    :pingluntaouxiang,
                    :pinglunshijian,
                    :pinglunneirong,
                    :shangpinleixing,
                    :pinglunshipinjietu,
                    :pinglunshipin,
                    :haopingtupian
                    )'''
    cursor.execute(insert_data_sql,
                {
                'shangpinid': id,
                'pinglunren': a,
                'pingluntaouxiang': b,
                'pinglunshijian': c,
                'pinglunneirong': d,
                'shangpinleixing': e,
                'pinglunshipinjietu': f,
                'pinglunshipin': g,
                'haopingtupian': h
                })

#把这两个函数不能直接用,把他门直接复制到想使用的地方
def opendb():
    dbpath = os.path.join(BASE_DIR, 'templates\\tb.db')
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()

def closedb(conn,cursor):
    conn.commit()
    cursor.close()





#查询表内数据
def searchdb(cursor):
    '''查询语句'''
    search_sql = "select ID,评论人,评论头像,评论时间,评论内容,购买商品类型,评论视频截图,评论视频,好评图片 from happy"
    results = cursor.execute(search_sql)
    all_results = results.fetchall()
    # for row in all_results:
        # print(type(row))  # type:tuple
        # print(list(row))  # type:list
    return all_results
#根据id查询表内符合的数据
def searchID(cursor,itemid):
    '''查询语句'''
    search_sql = f"SELECT * FROM happy WHERE ID = {itemid}"
    results = cursor.execute(search_sql)
    all_results = results.fetchall()
    # for row in all_results:
        # print(type(row))  # type:tuple
        # print(list(row))  # type:list
    print(all_results)
    return all_results

#删除表内某个商品id的所有评论的内容    delete(cursor,'588976817331')
def delete(cursor,itemid):
    delete_sql = f'delete from happy where id = {itemid}'
    cursor.execute(delete_sql)


# 删除一整个表格    deleteTable(cursor, "happy")
def deleteTable(cursor, tablename):
    delete_all_sql = f'drop table {tablename}'
    cursor.execute(delete_all_sql)


if __name__ == '__main__':
    dbpath = os.path.join(BASE_DIR, 'templates\\tb.db')
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()
    list =searchdb(cursor)
    print(list)

    conn.commit()
    cursor.close()